package com.gdwp;

public class Sqls {
    static String sql = "\n" +
            " select\n" +
            " zy.sheng_mc 省,\n" +
            " zy.shi_mc 市,\n" +
            " zy.qx_mc 县,\n" +
            " x.xmmc 建设项目名称,\n" +
            " null  建设用地批准时间,\n" +
            " null 建设用地批准文号,\n" +
            " null 建设用地批准机关,\n" +
            " x.zy_gdmj 水库淹没区占用耕地面积,\n" +
            " concat(\"'\", x.xmbh) 挂钩编号,\n" +
            " case when (select max(t.create_time) from farmland.skymk_log t where t.recod_type =1 and x.xmbh = t.bh collate utf8mb4_general_ci) is null then x.update_time  else (select max(t.create_time) from farmland.skymk_log t where t.recod_type =1 and  x.xmbh = t.bh collate utf8mb4_general_ci) end 项目预挂钩时间,\n" +
            "-- case when  x.check_state =3 then '预挂钩'  when  x.check_state =1 then '已挂钩'  end 挂钩时间,\n" +
            " concat(\"'\",c.tb_bsm) 补充耕地地块编号,\n" +
            " rf.dkbh 地块编号,\n" +
            " rf.gdtbdlmj  补充耕地地块面积,\n" +
            " rf.wchfsj  补充耕地地块入库时间,\n" +
            "        x.nf 项目年份,\n" +
            "        c.sheng_mc 省,\n" +
            "        c.shi_mc 市,\n" +
            "        c.qx_mc 县,\n" +
            "  \n" +
            "  rf.update_time 地块入库时间,\n" +
            "          c.tb_sjnf 图斑年份,\n" +
            "             c.tb_dlmc 流入耕地来源,\n" +
            "            d.update_time 地块挂钩时间,\n" +
            "case when (select max(t.create_time) from farmland.skymk_log t where t.recod_type =1 and x.xmbh = t.bh collate utf8mb4_general_ci) is null then x.update_time  else (select max(t.create_time) from farmland.skymk_log t where t.recod_type =1 and  x.xmbh = t.bh collate utf8mb4_general_ci) end 项目预挂钩时间,\n" +
            "case when  x.check_state =3 then '预挂钩'  when  x.check_state =1 then '已挂钩'  end 项目预挂钩状态,\n" +
            "t1.gdwp_type 建设项目审批状态,\n" +
            "t1.create_time 建设项目审批时间,\n" +
            "case when d.sf_gg = 1 then '已挂钩'  else null end 是否挂钩\n" +
            "        from rc_lr c\n" +
            "        inner join ss_balance_file sbf on sbf.file_type = 2 and sbf.is_delete =0 and sbf.business_id = c.id\n" +
            "        inner join (select rf.lr_id,rf.dkbh ,sum(ifnull( rf.gdtbdlmj,0)) gdtbdlmj,max(rf.wchfsj ) wchfsj\n" +
            "                         , min(rf.report_state) report_state, min(rf.audit_state) audit_state,max(rf.update_time) update_time\n" +
            "        from rc_lr_form rf where rf.sf_bz = 1 and rf.IS_DELETE = 0\n" +
            "        group by rf.lr_id,rf.dkbh ) rf on rf.lr_id= c.id and sbf.tbId =rf.dkbh\n" +
            "        left join rc_lr_dk d on d.lr_id =c.id and d.dkbh =rf.dkbh\n" +
            "        left join ba_xm x on d.ba_id = x.id and x.is_delete =0\n" +
            "        left join (SELECT group_concat(distinct zy.sheng_mc SEPARATOR ',') sheng_mc,group_concat(distinct zy.shi_mc SEPARATOR ',') shi_mc,group_concat(distinct zy.qx_mc SEPARATOR ',') qx_mc, zy.xm_id FROM ba_xm_zy zy group by zy.xm_id) zy on zy.xm_id = x.id\n" +
            "        left join (select case when t1.gdwp_type is null or t1.gdwp_type='' or t1.gdwp_type='3' then '未受理'\n" +
            "            when t1.gdwp_type='3' then '未受理'\n" +
            "            when t1.gdwp_type='1' then '已受理'\n" +
            "            when t1.gdwp_type='2' then '已退件'\n" +
            "            when t1.gdwp_type='4' then '已接件'\n" +
            "            when t1.gdwp_type='5' then '已补正' else '未受理' end gdwp_type,t2.create_time,t1.xmbh\n" +
            "            from farmland.approval_log_tab t1\n" +
            "        inner join (\n" +
            "        select xmbh, max(call_time) as create_time from farmland.approval_log_tab tab where tab.call_type = 1 and tab.call_logo = 2\n" +
            "        group by xmbh\n" +
            "        ) t2\n" +
            "        on t1.xmbh = t2.xmbh and t1.call_time = t2.create_time\n" +
            "        where t1.call_type = 1 and t1.call_logo = 2 ) t1 on t1.xmbh = x.xmbh \n" +
            "\twhere x.xmbh in ('%s');\n" +
            "\t";
}
